#!/usr/bin/python  
#-*- coding:utf-8 -*-  
############################  
#File Name: mysql.py
#Author: Timy.Huang 
#Mail: timy.huang@foxmail.com  
#Created Time: 2017-06-04 13:38:50
############################  

import commands, ConfigParser
import xml.etree.ElementTree as ET
from xml.dom.minidom import Document

def jointArgs(  args, encloseL = "", encloseR = "", split = "," ):
    _str = encloseL + str(args[0]) + encloseR
    for arg in args[1:]:
        _str = _str + split + encloseL + str(arg) + encloseR
    return _str

def createLimiterCmd( limiter = ""):
    comdLimiter = ""
    if len(limiter)>0:
        dictItems = limiter.items()
        for item in dictItems:
            if item[0] == "ID" and item[0] != "":
                comdLimiter = comdLimiter + item[0] + " = " + jointArgs([item[1]], encloseL = "", encloseR = "") + " and "
            else:
                comdLimiter = comdLimiter + item[0] + " like " + jointArgs([item[1]], encloseL = "\'", encloseR = "\'") + " and "
        comdLimiter = 'where ' + comdLimiter[:-4]
    return comdLimiter

def search( database, fields, limiter ):
    cmdLimiter = createLimiterCmd( limiter )
    cmdFields = jointArgs( fields )
    selectCmd = "select %s from %s.%s %s" % ( cmdFields, database[ 'database' ], database[ "table" ], cmdLimiter)
    sshCmd = "mysql --login-path=local -X -e \""  + selectCmd + " \""
    status,raw_result = commands.getstatusoutput( sshCmd )
    result_root_node = ET.fromstring( raw_result )
    rows = result_root_node.findall("row" )
    
    doc = Document()
    root_node = doc.createElement( "root" ) 
    doc.appendChild( root_node )
    root_node.setAttribute( "module", "search_result" )

    database_node = doc.createElement( "database" )
    for field in database:
        field_node = doc.createElement( "field" )
        field_node.setAttribute( "name", field )
        field_text = doc.createTextNode( database[ field ] )
        field_node.appendChild( field_text )
        database_node.appendChild( field_node )
    root_node.appendChild( database_node )

    fields_node = doc.createElement( "fields" )
    for field in fields:
        field_node = doc.createElement( "field" )
        field_text = doc.createTextNode( field )
        field_node.appendChild( field_text )
        fields_node.appendChild( field_node )
    root_node.appendChild( fields_node )

    result_node = doc.createElement( "result" )
    for row in rows:
        row_node = doc.createElement( "row" )
        for field in row:
            field_node = doc.createElement( "field" )
            field_text = doc.createTextNode( field.text )
            field_node.appendChild( field_text )
            field_node.setAttribute( "name", field.attrib[ "name" ] )
            row_node.appendChild( field_node )
        result_node.appendChild( row_node )
    root_node.appendChild( result_node )
    result = doc.toprettyxml()


    return result

if __name__ == "__main__":
    database = {"database":"test", "table":"test" }
    fields = [ "id", "name", "age" ]
    limiter = { "ID":"2" }
    print search( database, fields, limiter )
